package com.gitee.deeround.jdbchelper.core.dialect.mysql;

import com.gitee.deeround.jdbchelper.core.Dialect;
import com.gitee.deeround.jdbchelper.core.dialect.BaseDialectCore;
import com.gitee.deeround.jdbchelper.out.DataType;
import com.gitee.deeround.jdbchelper.out.DbColumn;
import com.gitee.deeround.jdbchelper.out.DbTable;
import com.gitee.deeround.jdbchelper.util.StringUtils;

import java.util.*;

/**
 * Mysql数据库方言实现
 *
 * @author deeround
 * @date 2024/03/29
 */
public class MysqlDialect extends BaseDialectCore implements Dialect {

    @Override
    public String getCountSql(String sql) {
        return "SELECT COUNT(*) JH_COUNT FROM ( " + sql + " ) JH_TB ";
    }

    @Override
    public String getPageSql(String sql, int pageNum, int pageSize) {
        return sql + " LIMIT " + ((pageNum - 1) * pageSize) + ", " + pageSize + " ";
    }

    @Override
    public Map<DataType, String> getDataType() {
        Map<DataType, String> types = new HashMap<>();
        types.put(DataType.String, "varchar");
        types.put(DataType.Date, "datetime");
        types.put(DataType.Clob, "text");
        types.put(DataType.Blob, "blob");
        types.put(DataType.Integer, "int");
        types.put(DataType.Decimal, "decimal");
        types.put(DataType.Object, "");
        types.put(DataType.Char, "char");
        return types;
    }

    @Override
    public String getTableColumn(String tableName) {
        return "select column_name AS COLNAME, ordinal_position AS COLPOSITION, column_default AS DATADEFAULT, is_nullable AS NULLABLE, data_type AS DATATYPE, " +
                "character_maximum_length AS DATALENGTH, numeric_precision AS DATAPRECISION, numeric_scale AS DATASCALE, column_key AS COLKEY, column_comment AS COLCOMMENT " +
                "from information_schema.columns where table_schema=DATABASE() and table_name = '" + tableName + "' order by ordinal_position ";
    }

    @Override
    public String existsTable(String tableName) {
        return "select count(*) JH_COUNT from information_schema.tables where table_schema=DATABASE() and upper(table_name)=upper('" + tableName + "')";
    }

    @Override
    public String existsTableColumn(String tableName, String columnName) {
        return "select count(*) JH_COUNT from information_schema.columns where table_schema=DATABASE() and upper(table_name)=upper('" + tableName + "') and upper(column_name)=upper('" + columnName + "')";
    }

    @Override
    public List<String> createTable(DbTable dbTable) {
        StringBuilder createTableSql = new StringBuilder();
        createTableSql.append("CREATE TABLE ").append(dbTable.getTableName()).append("(");
        StringBuilder primaryStrSql = new StringBuilder(" PRIMARY KEY (");
        for (int i = 0; i < dbTable.getColumnList().size(); i++) {
            DbColumn dbColumn = dbTable.getColumnList().get(i);
            String dateTypeStr = resolveDataType(dbColumn.getDataType(), dbColumn.getDataLength(), dbColumn.getDataScale());
            createTableSql.append(" ").append(dbColumn.getColumnName()).append(" ").append(dateTypeStr).append(" ");
            if (!"1".equals(dbColumn.getColumnNullable())) {
                createTableSql.append(" NOT NULL");
            }

            if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
                createTableSql.append(" DEFAULT ").append(dbColumn.getDataDefault());
            }
            if ("1".equals(dbColumn.getColumnKey())) {
                if (primaryStrSql.toString().endsWith("(")) {
                    primaryStrSql.append(dbColumn.getColumnName());
                } else {
                    primaryStrSql.append(",").append(dbColumn.getColumnName());
                }
            }
            createTableSql.append(" , ");
            if (i == dbTable.getColumnList().size() - 1) {
                primaryStrSql.append(")");
                createTableSql.append(primaryStrSql);
            }
        }
        createTableSql.append(")").append("  COMMENT '").append(dbTable.getTableComment()).append("'");
        return Collections.singletonList(createTableSql.toString());
    }

    @Override
    public List<String> dropTable(String tableName) {
        return Collections.singletonList("drop table " + tableName);
    }

    @Override
    public List<String> copyTable(String tableName, String newTableName) {
        return Collections.singletonList("CREATE TABLE " + newTableName + " AS SELECT * FROM " + tableName + " where 1=2");
    }

    @Override
    public List<String> modifyTableComment(String tableName, String tableComment) {
        return Collections.singletonList("alter table " + tableName + " comment '" + tableComment + "'");
    }

    @Override
    public List<String> modifyTableName(String oldTableName, String newTableName, String tableComment) {
        List<String> sqls = new ArrayList<>();
        sqls.add("alter table " + oldTableName + " rename to " + newTableName);
        if (!StringUtils.isEmpty(tableComment)) {
            sqls.addAll(modifyTableComment(newTableName, tableComment));
        }
        return sqls;
    }

    @Override
    public List<String> addTableColumn(DbColumn dbColumn) {
        List<String> sqls = new ArrayList<>();
        String dateTypeStr = resolveDataType(dbColumn.getDataType(), dbColumn.getDataLength(), dbColumn.getDataScale());
        StringBuilder sb = new StringBuilder();
        sb.append("alter table " + dbColumn.getTableName() + " add " + dbColumn.getColumnName() + " " + dateTypeStr);
        if (!"1".equals(dbColumn.getColumnNullable())) {
            sb.append(" not null");
        }
        if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
            sb.append(" default " + dbColumn.getDataDefault());
        }
        if (!StringUtils.isEmpty(dbColumn.getColumnComment())) {
            sb.append(" comment '" + dbColumn.getColumnComment() + "'");
        }
        sqls.add(sb.toString());
        return sqls;
    }

    @Override
    public List<String> modifyTableColumn(DbColumn dbColumn) {
        List<String> sqls = new ArrayList<>();
        String dateTypeStr = resolveDataType(dbColumn.getDataType(), dbColumn.getDataLength(), dbColumn.getDataScale());
        StringBuilder sb = new StringBuilder();
        sb.append("alter table " + dbColumn.getTableName() + " modify " + dbColumn.getColumnName() + " " + dateTypeStr);
        if (!"1".equals(dbColumn.getColumnNullable())) {
            sb.append(" not null");
        } else {
            sb.append(" null");
        }
        if (!StringUtils.isEmpty(dbColumn.getColumnComment())) {
            sb.append(" comment '" + dbColumn.getColumnComment() + "'");
        } else {
            sb.append(" comment ''");
        }
        sqls.add(sb.toString());

        if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
            sqls.add("alter table " + dbColumn.getTableName() + " ALTER COLUMN " + dbColumn.getColumnName() + " SET DEFAULT " + dbColumn.getDataDefault());
        } else {
            sqls.add("alter table " + dbColumn.getTableName() + " ALTER COLUMN " + dbColumn.getColumnName() + " DROP DEFAULT");
        }
        return sqls;
    }

    @Override
    public List<String> removeTableColumn(String tableName, String columnName) {
        return Collections.singletonList("alter table " + tableName + " drop " + columnName);
    }

    @Override
    public List<String> mergeInto(String tableName, String newData, String idColumn, List<String> insertColumns, List<String> updateColumns) {

        newData = "select * from ( " + newData + " ) _temp1";
        String newData1 = "select * from ( select * from ( " + newData + " ) _temp1 where " + idColumn + " not in ( select " + idColumn + " from " + tableName + " ) ) _temp2";

        //先更新
        StringBuilder updateSqlColumn = new StringBuilder();
        for (String updateColumn : updateColumns) {
            updateSqlColumn.append(",t.").append(updateColumn).append("=n.").append(updateColumn);
        }
        StringBuilder updateSql = new StringBuilder();
        updateSql.append("UPDATE " + tableName + " t ");
        updateSql.append("JOIN (" + newData + ") n on t." + idColumn + "=n." + idColumn + " ");
        updateSql.append("SET " + updateSqlColumn.substring(1));

        //插入正式表
        StringBuilder insertSqlColumn = new StringBuilder();
        for (String insertColumn : insertColumns) {
            insertSqlColumn.append(",").append(insertColumn);
        }
        String insertSql = "insert into " + tableName + "(" + insertSqlColumn.substring(1) + ") select " + insertSqlColumn.substring(1) + " from ( " + newData1 + " ) n";

        List<String> sqls = new ArrayList<>();
        sqls.add(updateSql.toString());
        sqls.add(insertSql);

        return sqls;
    }

    @Override
    public String recursiveSub(String tableName, String idColumn, String pidColumn, List<String> columns, String idValue) {
//        WITH RECURSIVE cte AS (
//                SELECT a.dept_code,a.dept_name,a.parent_code
//        FROM sys_dept a
//        WHERE a.dept_code = '00927643300001' -- 这里替换为你想要查询的节点的ID
//        UNION ALL
//        SELECT b.dept_code,b.dept_name, b.parent_code
//        FROM sys_dept b
//        JOIN cte c ON b.parent_code = c.dept_code
//)
//        SELECT * FROM cte;

        StringBuilder aColumns = new StringBuilder();
        StringBuilder bColumns = new StringBuilder();
        for (String column : columns) {
            aColumns.append(",a." + column);
            bColumns.append(",b." + column);
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" WITH RECURSIVE cte_" + tableName + " AS ( ")
                .append(" SELECT " + "1 lev" + aColumns + " ")
                .append(" FROM " + tableName + " a ")
                .append(" WHERE a." + idColumn + " = '" + idValue + "' ")
                .append(" UNION ALL ")
                .append(" SELECT " + "c.lev+1 lev" + bColumns + " ")
                .append(" FROM " + tableName + " b ")
                .append(" JOIN cte_" + tableName + " c ON b." + pidColumn + " = c." + idColumn + " ")
                .append(" ) ");

        return sb.toString();
    }

    @Override
    public String funDate() {
        return "now()";
    }

    @Override
    public String funTimestamp() {
        return "current_timestamp()";
    }

    @Override
    public String funGuid() {
        return "uuid()";
    }

    @Override
    public String funWmConcat(String columnName) {
        return "group_concat(" + columnName + ")";
    }

}
